package com.zjht.gmcc.bean.action;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.LinkedHashMap;
import java.util.Map;

import com.zjht.gmcc.bean.util.MyConstant;
import com.zjht.gmcc.bean.util.MyJDBC;

/**
 * 采购订单相关方法
 * @author dyh 2011-01-28
 */
public class OrderUtil implements java.io.Serializable {
	private static final long serialVersionUID = 1923151818657052800L;

	public static final String ORDER_STATUS_STOP = "0";// 已终止
	public static final String ORDER_STATUS_OK = "1";// 执行中
	public static final String ORDER_STATUS_SOON_STOP = "2";// 快到期

	public static final String ORDER_TYPE_COMMON = "1";// 普通采购订单
	public static final String ORDER_TYPE_FD = "2";// 集采订单

	/**
	 * 获取厂家当前最新的非集采采购订单ID
	 * @author dyh 2011-01-28
	 * @param vendorid 厂家ID
	 * @return 厂家当前最新的非集采采购订单ID
	 */
	public static int getOrderIDNew(int vendorid) {
		int id = MyConstant.INT_NULL;
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();
			String sql = "SELECT orderid FROM ORDERS WHERE vendorid=? AND status=? AND ordertype=? ORDER BY orderid DESC";
			PreparedStatement ps = myJDBC.prepareStatement(sql);
			ps.setInt(1, vendorid);
			ps.setString(2, ORDER_STATUS_OK);
			ps.setString(3, ORDER_TYPE_COMMON);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				id = rs.getInt(1);
			}
			rs.close();
			rs = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}
		return id;
	}

	/**
	 * 获取厂家当前最新的非集采采购订单名称
	 * @author dyh 2011-01-28
	 * @param vendorid 厂家ID
	 * @return 厂家当前最新的非集采采购订单名称
	 */
	public static String getOrderNONew(int vendorid) {
		String orderno = "";
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();
			String sql = "SELECT orderno FROM ORDERS WHERE vendorid=? AND status=? AND ordertype=? ORDER BY orderid DESC";
			PreparedStatement ps = myJDBC.prepareStatement(sql);
			ps.setInt(1, vendorid);
			ps.setString(2, ORDER_STATUS_OK);
			ps.setString(3, ORDER_TYPE_COMMON);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				orderno = rs.getString(1);
			}
			rs.close();
			rs = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}
		return orderno;
	}

	/**
	 * 获取订单名称
	 * @author dyh 2011-01-31
	 * @param orderid 订单ID
	 * @return 订单名称
	 */
	public static String getOrderNO(int orderid) {
		String orderno = "";
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();
			String sql = "SELECT orderno FROM ORDERS WHERE orderid=?";
			PreparedStatement ps = myJDBC.prepareStatement(sql);
			ps.setInt(1, orderid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				orderno = rs.getString(1);
			}
			rs.close();
			rs = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}
		return orderno;
	}

	/**
	 * 获取集采订单批次号列表
	 * @author dyh 2011-01-28
	 * @param showAllPlanNo 是否显示所有批次号(true:除手工停用外的所有批次;false:已发布批次)
	 * @return 订单批次号列表Map<String,String>=<provid,planno>
	 */
	public static Map<String, String> getFDOrderNoList(int vendorid) {
		Map<String, String> hPlanNo = null;
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();
			String sql = "SELECT orderid,orderno FROM ORDERS WHERE vendorid=? AND status=? AND ordertype=? ORDER BY orderno DESC";
			PreparedStatement ps = myJDBC.prepareStatement(sql);
			ps.setInt(1, vendorid);
			ps.setString(2, ORDER_STATUS_OK);
			ps.setString(3, ORDER_TYPE_FD);
			ResultSet rs = ps.executeQuery();
			hPlanNo = new LinkedHashMap<String, String>();
			while (rs.next()) {
				hPlanNo.put(rs.getString(1), rs.getString(2));
			}
			rs.close();
			rs = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}
		return hPlanNo;
	}

	/**
	 * 获取订单剩余数量
	 * @author dyh 2011-01-28
	 * @param orderid 订单ID
	 * @param productid 产品ID
	 * @return 厂家当前最新的采购订单名称
	 */
	public static int getOrderRemainAmount(int orderid, String productid) {
		int amount = 0;
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();
			String sql = "SELECT NVL(QUANTITY-inputamount,0)  FROM OrderDetail WHERE OrderID = ? AND productid=?";
			PreparedStatement ps = myJDBC.prepareStatement(sql);
			ps.setInt(1, orderid);
			ps.setString(2, productid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				amount = rs.getInt(1);
			}
			rs.close();
			rs = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}
		return amount;
	}

	/**
	 * 获取合同类型
	 * @author 孙汉斌 2011-11-25
	 * @param ordertypeid 合同ID
	 * @return OrderTypeName 合同类型
	 */
	public static String getOrderTypeName(String ordertypeid) {
		String OrderTypeName = "";
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();
			String sql = "SELECT ordertypename FROM ORDERTYPE WHERE ordertypeid=?";
			PreparedStatement ps = myJDBC.prepareStatement(sql);
			ps.setString(1, ordertypeid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				OrderTypeName = rs.getString(1);
			}
			rs.close();
			rs = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}
		return OrderTypeName;
	}

	/**
	 * 获取产品类别名称(合作范围)
	 * @author 孙汉斌 2011-11-29
	 * @param typeid 产品类别ID
	 * @return typename 产品类别名称
	 */

	public static String getProductTypeName(String typeid) {
		String prdTypeName = "";
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();
			String sql = "SELECT typename FROM PRODUCTTYPE WHERE typeid=?";
			PreparedStatement ps = myJDBC.prepareStatement(sql);
			ps.setString(1, typeid);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				prdTypeName = rs.getString(1);
			}
			rs.close();
			rs = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}
		return prdTypeName;
	}

	/**
	 * 获取合同状态
	 * @author 孙汉斌 2011-12-05
	 * @param status
	 * @return s_status
	 */
	public static String getOrderStaus(String status) {
		String s_status = "";
		if (ORDER_STATUS_STOP.equals(status)) {
			s_status = "<font color='green'><b>已终止</b></font>";
		} else if (ORDER_STATUS_OK.equals(status)) {
			s_status = "执行中";
		} else if (ORDER_STATUS_SOON_STOP.equals(status)) {
			s_status = "<font color='red'><b>快到期</b></font>";
		}
		return s_status;
	}

	/**
	 * 初始化合同状态
	 * @author 孙汉斌 2011-12-09
	 */
	public static void initOrderStatus() {
		MyJDBC myJDBC = null;
		try {
			myJDBC = new MyJDBC();
			PreparedStatement ps = null;
			StringBuilder sql = new StringBuilder();
			// 开启合同(状态->1)
			sql
					.append("UPDATE Orders SET status='1' WHERE orderid IN (SELECT orderid FROM Orders WHERE TO_DATE(startdate,'yyyy-mm-dd')=TRUNC(SYSDATE) AND STATUS='0')");
			ps = myJDBC.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
			ps = null;
			// 已终止(截止日期已过 状态->0)
			sql = new StringBuilder();
			sql
					.append("UPDATE Orders SET status='0' WHERE orderid IN(SELECT orderid FROM Orders WHERE (TRUNC(SYSDATE)-TO_DATE(enddate,'yyyy-mm-dd'))>0 AND status <>'0')");
			ps = myJDBC.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
			ps = null;
			// 快到期(状态->2)
			sql = new StringBuilder();
			sql
					.append("UPDATE ORDERS SET STATUS='2' WHERE ORDERID IN(SELECT orderid FROM ORDERS WHERE (to_date(enddate,'yyyy-mm-dd')-TRUNC(sysdate))<=30 AND (TO_DATE(enddate,'yyyy-mm-dd')-TRUNC(sysdate))>0 AND status <>'0')");
			ps = myJDBC.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
			ps = null;

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}
	}

	/**
	 * 初始化供应商合同状态,若存在合同,则orderstatus设置为'1',若不存在合作合同,则改为'0'
	 * @author 孙汉斌 2011-12-09
	 */

	public static void initProOrderStatus() {
		MyJDBC myJDBC = null;
		PreparedStatement ps = null;
		try {
			myJDBC = new MyJDBC();
			StringBuilder sql = new StringBuilder();
			sql
					.append("UPDATE Provider p SET p.orderstatus='1' WHERE p.providerid IN (SELECT o.providerid FROM Orders o WHERE o.status='1' AND o.providerid = p.providerid)");
			ps = myJDBC.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
			ps = null;
			sql = new StringBuilder();
			sql.append("UPDATE Provider p SET p.ORDERSTATUS='0' WHERE p.PROVIDERID IN (SELECT r.providerid FROM Orders r WHERE r.providerid ");
			sql
					.append("NOT IN(SELECT o.providerid FROM Orders o WHERE (o.status='1' OR o.status='2') AND o.providerid=r.providerid GROUP BY o.providerid) AND r.providerid=p.providerid ");
			sql.append("GROUP BY r.providerid)");
			ps = myJDBC.prepareStatement(sql);
			ps.executeUpdate();
			ps.close();
			ps = null;
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			MyJDBC.close(myJDBC);
		}

	}

}